package com.twinkle.operator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class ClsManager {
    public List<Stu> selectStuByCls(int clsId) {
        List<Stu> list = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConn();
            String sql = "SELECT stu_id, stu_no, stu_name, fk_cls_id FROM stu WHERE fk_cls_id = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, clsId);
            resultSet = preparedStatement.executeQuery();
            list = new ArrayList<>();
            while (resultSet.next()) {
                Stu stu = new Stu();
                stu.setClsId(resultSet.getInt("fk_cls_id"));
                stu.setStuId(resultSet.getInt("stu_id"));
                stu.setStuName(resultSet.getString("stu_name"));
                stu.setStuNo(resultSet.getString("stu_no"));
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(resultSet, preparedStatement, connection);
        }
        return list;
    }

    public List<Cls> selectClsAll() {
        List<Cls> list = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConn();
            String sql = "SELECT cls_id, cls_no, cls_name, cls_beginTime FROM cls;";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            list = new ArrayList<>();
            while (resultSet.next()) {
                Cls cls = new Cls();
                cls.setClsId(resultSet.getInt("cls_id"));
                cls.setClsNo(resultSet.getString("cls_no"));
                cls.setClsName(resultSet.getString("cls_name"));
                cls.setClsBeginTime(resultSet.getTimestamp("cls_beginTime"));
                list.add(cls);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(resultSet, preparedStatement, connection);
        }
        return list;
    }

    public Integer selectClsByClsNo(String clsNo) {
        Integer clsId = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConn();
            String sql = "SELECT cls_id FROM cls WHERE cls_no=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, clsNo);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                clsId = resultSet.getInt("cls_id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(resultSet, preparedStatement, connection);
        }
        return clsId;
    }

    public boolean closeCls(int clsId) {
        boolean rt = false;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            connection.setAutoCommit(false);
            String sql = "DELETE FROM stu WHERE fk_cls_id=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, clsId);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            sql = "DELETE FROM cls WHERE cls_id=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, clsId);
            preparedStatement.executeUpdate();
            connection.commit();
            rt = true;
        } catch (SQLException e) {
            try {
                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            DBUtil.closeAll(null, preparedStatement, connection);
        }
        return rt;
    }

    public boolean openCls(Cls cls) {
        boolean rt = false;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            String sql = "INSERT INTO cls(cls_no,cls_name,cls_beginTime) VALUES (?,?,?);";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, cls.getClsNo());
            preparedStatement.setString(2, cls.getClsName());
            preparedStatement.setTimestamp(3, new Timestamp(cls.getClsBeginTime().getTime()));
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                rt = true;
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null, preparedStatement, connection);
        }
        return rt;
    }

    public boolean addStu(Stu stu) {
        boolean rt = false;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConn();
            String sql = "INSERT INTO stu(stu_no,stu_name,fk_cls_id) VALUES (?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, stu.getStuNo());
            preparedStatement.setString(2, stu.getStuName());
            preparedStatement.setInt(3, stu.getClsId());
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                rt = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null, preparedStatement, connection);
        }
        return rt;
    }
}
